<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Statistic_mod extends CI_Model {

    private $_dbTableMessages = 'messages';

    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    public function getStatistic($filter = NULL) {
        $where = "";
        if (isset($filter['date_from']) && $filter['date_from'] != "") {

            $where .= "( ";
            $where .= $this->_dbTableMessages . ".time >= '" . $filter['date_from'];
            $where .= "' )";
        }
        if (isset($filter['date_to']) && $filter['date_to'] != "") {
            if ($where != "") {
                $where .= " AND ";
            }
            $where .= "( " . $this->_dbTableMessages . ".time <='" . $filter['date_to'];
            $where .= "' ) ";
        }

        $this->db->select("COUNT(*) as num_out, DATE(time) as date, time");
        $this->db->from($this->_dbTableMessages);
        if ($where != "")
            $this->db->where($where);
        $this->db->order_by('DATE(time)', 'desc');
        $this->db->group_by('DATE(time)');
        $query = $this->db->get();
        $dates = $query->result();
        $result = array();
        $where = $this->_dbTableMessages . ".seen = '1'";

        foreach ($dates as $date) {
            $row = new stdClass();
            $row->date = $date->date;
            $row->num_out = $date->num_out;
            $row->num_in = $this->getNumIn($date->date);
            //$row->num_out_by_hours=  array();
            $row->num_out_by_hours = $this->getByHours($date->date, "");
            $row->num_in_by_hours = $this->getByHours($date->date, $where);
            $result[] = $row;
        }

        return $result;
    }

    private function getNumIn($date) {
        $this->db->from($this->_dbTableMessages);
        $this->db->where("DATE(" . $this->_dbTableMessages . ".time) ='" . $date . "' AND " . $this->_dbTableMessages . ".seen = '1'");
        $num_seen = $this->db->count_all_results();

        return $num_seen;
    }

    private function getByHours($date, $where) {
        $select = "SELECT ";
        for ($i = 0; $i < 24; $i++) {
            $select.= "(SELECT COUNT(*) FROM {$this->_dbTableMessages} WHERE HOUR({$this->_dbTableMessages}.`time`) = " . $i;
            $select.=" AND DATE({$this->_dbTableMessages}.`time`) ='" . $date . "'";
            if ($where != "") {
                $select .= " AND " . $where;
            }
            $select.=") as hour_" . $i;
            if ($i != 23)
                $select.= " , ";
        }

        $query = $this->db->query($select);

        return $query->first_row();
    }

}

?>
